# 文件名: app/models/database.py
import datetime
import sys
from zoneinfo import ZoneInfo

from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, ForeignKey, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import os
from dotenv import load_dotenv

from app.utils.logging_config import logger

# 加载环境变量
load_dotenv()

# 获取本地时区
local_timezone = datetime.datetime.now().astimezone().tzinfo


# 创建一个获取当前本地时间的函数
def get_local_time():
    return datetime.datetime.now(local_timezone)


# 获取应用的实际运行路径（而不是临时解压目录）
def get_real_path():
    if getattr(sys, 'frozen', False):
        # 如果是打包环境，获取exe文件所在目录
        return os.path.dirname(sys.executable)
    else:
        # 如果是开发环境，获取项目根目录
        return os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))


# 创建数据库目录
DB_DIRECTORY = os.path.join(get_real_path(), "db")
os.makedirs(DB_DIRECTORY, exist_ok=True)

# 创建数据库路径
DB_PATH = os.path.join(DB_DIRECTORY, "app.db")

# 打印路径信息，便于调试
logger.info(f"数据库路径: {DB_PATH}")

# 创建数据库引擎
SQLALCHEMY_DATABASE_URL = f"sqlite:///{DB_PATH}"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})

# 创建会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基础类
Base = declarative_base()


# 获取数据库会话
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# 监控用户表
class MonitoredUser(Base):
    __tablename__ = "monitored_users"

    id = Column(Integer, primary_key=True, index=True)
    platform = Column(String, nullable=False)  # tiktok, douyin, instagram
    username = Column(String, nullable=False)
    sec_uid = Column(String, nullable=True)  # TikTok/Douyin使用
    user_id = Column(String, nullable=True)  # Instagram使用
    category = Column(String, nullable=True)  # 添加博主分类字段
    profile_info = Column(Text)  # 存储JSON格式的完整资料
    last_video_count = Column(Integer, default=0)
    created_at = Column(DateTime, default=get_local_time())
    last_checked = Column(DateTime, default=get_local_time())
    sequence_number = Column(String(50), nullable=True)
    is_open = Column(Boolean, default=True)

    videos = relationship("Video", back_populates="user")


# 视频表
class Video(Base):
    __tablename__ = "videos"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("monitored_users.id"))
    platform = Column(String, nullable=False)
    video_id = Column(String, nullable=False)
    title = Column(Text, nullable=True)
    download_url = Column(Text)  # 存储JSON格式的URL列表
    local_path = Column(String, nullable=True)
    download_status = Column(String, default="pending")  # pending, downloaded, failed
    last_download_url = Column(String, nullable=True)  # 用于手动下载的URL
    created_at = Column(DateTime, default=get_local_time())
    download_at = Column(DateTime)
    retry_count = Column(Integer, default=0)  # 重试次数
    next_retry_at = Column(DateTime)  # 下次重试时间
    duration = Column(Integer)  # 下次重试时间

    user = relationship("MonitoredUser", back_populates="videos")


# 添加系统设置模型
class SystemSetting(Base):
    __tablename__ = "system_settings"

    id = Column(Integer, primary_key=True)
    key = Column(String, unique=True, nullable=False)
    value = Column(String, nullable=True)
    description = Column(String, nullable=True)
    updated_at = Column(DateTime, default=get_local_time(), onupdate=get_local_time())

    @classmethod
    def get_value(cls, db, key, default=None):
        if db is None:  # 添加空检查
            logger.warn(f"警告: 调用 get_value 时数据库会话为空，返回默认值: {default}")
            return default

        try:
            # 确保事务处于活动状态
            if not db.is_active:
                db.rollback()

            setting = db.query(cls).filter(cls.key == key).first()
            if setting:
                return setting.value
            return default
        except Exception as e:
            logger.error(f"获取设置值时出错: {str(e)}")
            db.rollback()  # 确保回滚事务以防错误
            return default

    @classmethod
    def set_value(cls, db, key, value, description=None):
        if db is None:  # 添加空检查
            logger.warn(f"警告: 调用 set_value 时数据库会话为空，无法设置值")
            return None

        try:
            # 确保事务处于活动状态
            if not db.is_active:
                db.rollback()

            setting = db.query(cls).filter(cls.key == key).first()
            if setting:
                setting.value = value
                if description:
                    setting.description = description
            else:
                setting = cls(key=key, value=value, description=description)
                db.add(setting)
            db.commit()
            return setting
        except Exception as e:
            logger.error(f"设置值时出错: {str(e)}")
            db.rollback()
            return None


# 定时任务状态表
class SchedulerTask(Base):
    __tablename__ = "scheduler_tasks"

    id = Column(Integer, primary_key=True)
    task_id = Column(String, unique=True, nullable=False)  # 任务ID，如'check_tiktok_updates'
    task_name = Column(String, nullable=False)  # 任务名称，如'TikTok检查更新'
    status = Column(String, default="idle")  # 状态: idle, running, completed, failed
    last_run_at = Column(DateTime)  # 上次运行时间
    next_run_at = Column(DateTime)  # 下次计划运行时间
    last_result = Column(String)  # 上次运行结果
    created_at = Column(DateTime, default=get_local_time)
    updated_at = Column(DateTime, default=get_local_time, onupdate=get_local_time)

    # 修改 SchedulerTask 类的 update_task_status 方法
    @classmethod
    def update_task_status(cls, db, task_id, status, result=None):
        """更新任务状态"""
        try:
            task = db.query(cls).filter(cls.task_id == task_id).first()
            if not task:
                # 创建新任务记录
                task = cls(
                    task_id=task_id,
                    task_name=task_id.replace('_', ' ').title(),  # 默认名称
                    status=status,
                    last_run_at=get_local_time() if status in ["running", "completed", "failed"] else None
                )
                db.add(task)
            else:
                # 更新现有任务
                task.status = status
                if status in ["running", "completed", "failed"]:
                    task.last_run_at = get_local_time()
                if result is not None:
                    task.last_result = result

            # 获取下次计划运行时间
            try:
                from app.main import scheduler
                job = scheduler.get_job(task_id)
                if job:
                    # 使用 _get_run_times 方法或直接获取触发器的下一次运行时间
                    try:
                        # 方法1: 尝试使用 _get_run_times 方法
                        next_times = job._get_run_times(get_local_time())
                        if next_times and len(next_times) > 0:
                            task.next_run_at = next_times[0]
                    except:
                        # 方法2: 尝试从触发器直接获取
                        if hasattr(job, 'trigger') and hasattr(job.trigger, 'get_next_fire_time'):
                            next_fire_time = job.trigger.get_next_fire_time(None, get_local_time())
                            if next_fire_time:
                                task.next_run_at = next_fire_time
            except Exception as e:
                logger.error(f"获取任务 {task_id} 的下次计划运行时间失败: {str(e)}")

            db.commit()
            return task
        except Exception as e:
            db.rollback()
            logger.error(f"更新任务状态失败: {str(e)}")
            return None

    @classmethod
    def get_all_tasks(cls, db):
        """获取所有任务状态"""
        try:
            return db.query(cls).all()
        except Exception as e:
            logger.error(f"获取任务状态失败: {str(e)}")
            return []


# 热门视频表
class HotVideo(Base):
    __tablename__ = "hot_videos"

    id = Column(Integer, primary_key=True, index=True)
    platform = Column(String, nullable=False)  # 平台：douyin
    video_id = Column(String, nullable=False)  # 视频ID
    title = Column(Text, nullable=True)  # 视频标题
    cover_url = Column(Text, nullable=True)  # 封面URL
    duration = Column(Integer, nullable=True)  # 视频时长(毫秒)
    author = Column(String, nullable=True)  # 作者昵称
    avatar_url = Column(Text, nullable=True)  # 作者头像
    fans_count = Column(Integer, nullable=True)  # 粉丝数
    play_count = Column(Integer, nullable=True)  # 播放次数
    like_count = Column(Integer, nullable=True)  # 点赞数
    publish_time = Column(DateTime, nullable=True)  # 发布时间
    video_url = Column(Text, nullable=True)  # 视频URL
    download_url = Column(Text, nullable=True)  # 下载URL（JSON格式存储）
    download_status = Column(String, default="pending")  # 下载状态
    local_path = Column(String, nullable=True)  # 本地存储路径
    rank = Column(Integer, nullable=True)  # 排名
    created_at = Column(DateTime, default=get_local_time)  # 创建时间
    category = Column(String, nullable=True)  # 分类或标签
    is_favorite = Column(Boolean, default=False)  # 是否收藏
    is_selected = Column(Boolean, default=False)  # 是否被选中（用于批量操作）


# 创建数据库表
def create_tables():
    # 只有在表不存在时才创建
    if not os.path.exists(DB_PATH) or os.path.getsize(DB_PATH) == 0:
        Base.metadata.create_all(bind=engine)
        logger.info("数据库表已创建")
    else:
        logger.info("数据库已存在，跳过创建")
